#!/usr/bin/env python
# coding: utf-8

# In[ ]:


#finally used
import pandas as pd
from sympy import symbols, simplify

def calculate_equations_for_state(state_row):
    state_name = state_row['state']
    beta_coeffs = state_row[['beta1', 'beta2', 'beta3', 'beta4']].dropna().tolist()
    gamma_coeffs = state_row[['gamma1', 'gamma2', 'gamma3', 'gamma4', 'gamma5', 'gamma6', 'gamma7', 'gamma8']].dropna().tolist()
    
    num_iterations = 20
    mobility_t = symbols(f'mobility_t0:{num_iterations + len(beta_coeffs)}')
    lc_t = symbols(f'lc_t1:{num_iterations + len(gamma_coeffs) + 1}')
    
    equations = []
    coefficients_dict = {}
    for i in range(num_iterations):
        beta_term = sum(beta_coeffs[j] * mobility_t[i + j + 1] for j in range(len(beta_coeffs)))
        gamma_term = sum(gamma_coeffs[j] * lc_t[i + j + 1] for j in range(len(gamma_coeffs)))
        equations.append(beta_term + gamma_term)
    
    for i in range(1, num_iterations):
        equations[0] = equations[0].subs(mobility_t[i], equations[i])
    equation_final = simplify(equations[0])
    
    for term in equation_final.as_ordered_terms():
        coeff = term.as_coeff_Mul()[0]
        var = term.as_coeff_Mul()[1]
        coefficients_dict[str(var)] = coeff
    
    coefficients_dict['State'] = state_name
    return coefficients_dict


def main():
    # Define the path to the Excel file
    file_path = r'Yourpath\070624_coefficientforARDLtoIDL_48.xlsx'
    coefficients_df = pd.read_excel(file_path)
    
    # Apply the function to each row and collect results
    results = coefficients_df.apply(calculate_equations_for_state, axis=1).tolist()
    results_df = pd.DataFrame(results)
    
    # Save results to an Excel file in the same directory
    output_file_path = r'Yourpath\070624_IDL_equations_results_48.xlsx'
    results_df.to_excel(output_file_path, index=False)
    
    print(f"Results saved to {output_file_path}")

if __name__ == "__main__":
    main()
    


